--Delete database neu da ton tai.
USE master
GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'IMPRO')
DROP DATABASE IMPRO
GO

--Create database IMPRO.
CREATE DATABASE IMPRO
GO

USE IMPRO
GO


--Create tables
--01. Table Departments
CREATE TABLE Departments
(
        DEPTNO          INT				CONSTRAINT PK_DEPTNO PRIMARY KEY,
        DEPTNAME        NVARCHAR(20),
        DEPTHEAD        NVARCHAR(20),
        LOCATION        NVARCHAR(20)
)
GO

--02. Table DeptInterDependency
CREATE TABLE DeptInterdependency
(
        DEPTNO          INT				CONSTRAINT FK_DeptInter_DEPTNO FOREIGN KEY REFERENCES Departments(DEPTNO),
        UNDEPTNO        INT,
        DNDEPTNO        INT
)
GO


--03. Table DeptPosWeightage
CREATE TABLE DeptPosWeightage
(
        DEPTNO          INT				CONSTRAINT FK_DeptPos_DEPTNO FOREIGN KEY REFERENCES Departments(DEPTNO),
        LAYER           INT,
        WEIGHTAGE       INT
)
GO


--04. Table Sections
CREATE TABLE Sections
(
        SECTID          NVARCHAR(10)    CONSTRAINT PK_SECTID PRIMARY KEY,
        SECTNAME        NVARCHAR(20),
        SECTINCH        NVARCHAR(20),
        DEPTNO          INT             CONSTRAINT FK_Sect_DEPTNO FOREIGN KEY REFERENCES Departments(DEPTNO)
)
GO


--05. Table Designation
CREATE TABLE Designation
(
        DESIGNID        NVARCHAR(10)    CONSTRAINT PK_DESIGNID PRIMARY KEY,
        DESIGNATION     NVARCHAR(20)
)
GO


--06. Table DesignLayer
CREATE TABLE DesignLayer
(
        DESIGNID		NVARCHAR(10)	CONSTRAINT FK_DegnLay_DESGID FOREIGN KEY REFERENCES Designation(DESIGNID),
        LAYER           INT,
        WEIGHTAGE       INT

)
GO


--07. Table DesignationWeightage
CREATE TABLE DesignationWeightage
(
        DESIGNID		NVARCHAR(10)     CONSTRAINT FK_DegnWeig_DESGID FOREIGN KEY REFERENCES Designation(DESIGNID),
        DEPTNO          INT                             CONSTRAINT FK_DegnWeig_DEPTNO FOREIGN KEY REFERENCES Departments(DEPTNO),
        WEIGHTAGE       NVARCHAR(50)
)
GO

--08. Table Employees
CREATE TABLE Employees
(
        EMPID           NVARCHAR(10)    CONSTRAINT PK_EMPID PRIMARY KEY,
        EMPNAME         NVARCHAR(20),
        DEPTNO          INT             CONSTRAINT FK_Emp_DEPTNO FOREIGN KEY REFERENCES Departments(DEPTNO),
        DESIGNID        NVARCHAR(10)    CONSTRAINT FK_Emp_DESGID FOREIGN KEY REFERENCES Designation(DESIGNID),
        SECTID          NVARCHAR(10)    CONSTRAINT FK_Emp_SECTID FOREIGN KEY REFERENCES Sections(SECTID),
        ADDRESS         NVARCHAR(50),
        PHONE           NVARCHAR(15),
        FAX             NVARCHAR(15),
        EMAIL           NVARCHAR(50)            
)
GO

--09. Table JobRotation
CREATE TABLE JobRotation
(
        EMPID           NVARCHAR(10)    CONSTRAINT FK_JobRot_EMPID FOREIGN KEY REFERENCES Employees(EMPID),
        PRESENTDESIGNATION NVARCHAR(20),
        DEPUTEDTO       INT,
        STATUS          NVARCHAR(1),
        REMARKS         NVARCHAR(100)
)
GO

		

--10. Table Vacancies
CREATE TABLE Vacancies
(
        VACANCYID       NVARCHAR(10)    CONSTRAINT PK_VACANCYID PRIMARY KEY,
        DEPTNO          INT             CONSTRAINT FK_Vac_DEPTNO FOREIGN KEY REFERENCES Departments(DEPTNO),
        SECTID          NVARCHAR(10)    CONSTRAINT FK_Vac_SECTID FOREIGN KEY REFERENCES Sections(SECTID),
        DESIGNID        NVARCHAR(10)    CONSTRAINT FK_Vac_DESGID FOREIGN KEY REFERENCES Designation(DESIGNID),
        NOOFVACANCIES	INT,
        STATUS          NVARCHAR(1),
        VACANCYDATE     DATE,
        PRIORITY        NVARCHAR(50)
)
GO


--11. Table VacancyFillDetails
CREATE TABLE VacancyFillDetails
(
        VACANCYID       NVARCHAR(10)    CONSTRAINT FK_VacFill_VACID FOREIGN KEY REFERENCES Vacancies(VACANCYID),
        EMPID           NVARCHAR(10)    CONSTRAINT FK_VacFill_EMPID FOREIGN KEY REFERENCES Employees(EMPID),
        FILLEDDATE      DATE,
        INTAKEDETAILS	NVARCHAR(50),
		STATUS			NVARCHAR(1)
)
GO


--12. Table Account
CREATE TABLE Account
(
        USERNAME        VARCHAR(10),
        PASSWORD        VARCHAR(10),
        ACCESS          NVARCHAR(1),
		EMPID			NVARCHAR(10)	CONSTRAINT PK_Account_EMPID FOREIGN KEY REFERENCES Employees(EMPID)
)
GO

Insert into Departments values(1,'Maketing','Linh','QT')
Insert into Departments values(2,'Factory','Duy','VL')
Insert into Departments values(3,'Warehouse','Dang','LMX')
Insert into Departments values(4,'Office','Kiet','NQC')

Insert into Designation values('D1','Manager')
Insert into Designation values('D2','Programer')
Insert into Designation values('D3','Staff Office')
Insert into Designation values('D4','Director')

Insert into Sections values('S1','Human Manager','xyz',1)
Insert into Sections values('S2','eProject','zxy',2)
Insert into Sections values('S3','IMPRO Project','ghj',3)
Insert into Sections values('S4','Mobile Online','jkl',4)

Insert Into Employees values ('E1','linh',1,'D1','S1','luy ban bich','0123455678','0823453','linh@yahoo.com')
Insert Into Employees values ('E2','duy',2,'D2','S2','cong hoa','012332233','872282','duy@yahoo.com')
Insert Into Employees values ('E3','kiet',3,'D3','S3','au co','0922322453','898223','kiet@yahoo.com')
Insert Into Employees values ('E4','dang',4,'D4','S4','ten lua1','01227722122','5436435','linh@yahoo.com')
Insert Into Employees values ('E5','TEST1',4,'D2','S1','ten lua2','01227722122','5436435','linh@yahoo.com')
Insert Into Employees values ('E6','TEST2',3,'D4','S3','ten lua3','01227722122','5436435','linh@yahoo.com')
Insert Into Employees values ('E7','TEST4',2,'D2','S4','ten lua4','01227722122','5436435','linh@yahoo.com')
Insert Into Employees values ('E8','TEST3',1,'D3','S2','ten lua5','01227722122','5436435','linh@yahoo.com')

Insert into Account values('admin','admin','A', 'E1')
Insert into Account values('aaa','123','U', 'E2')
Insert into Account values('bbb','123','U', 'E3')
Insert into Account values('ccc','123','U', 'E4')

Insert Into Vacancies values('V1',1,'S1','D1',1,'1', '2013-11-09','max')
Insert Into Vacancies values('V2',2,'S2','D2',2,'1','2013-11-09','normal')
Insert Into Vacancies values('V3',3,'S3','D3',3,'1','2013-11-09','max')
Insert Into Vacancies values('V4',4,'S4','D4',4,'0','2013-11-09','normal')

Insert Into VacancyFillDetails values('V1','E1','2013-11-22','E1 Fill', '0')
Insert Into VacancyFillDetails values('V1','E2','2013-11-15','E2 Fill', '0')
Insert Into VacancyFillDetails values('V2','E3','2013-11-18','E3 Fill', '0')
Insert Into VacancyFillDetails values('V2','E4','2013-11-19','E4 Fill', '0')

INSERT INTO JobRotation VALUES('E1', 'Manager', '', '0', 'AAA')
INSERT INTO JobRotation VALUES('E2', 'Programer', '1', '0', 'BBB')
INSERT INTO JobRotation VALUES('E3', 'Staff Office', '2', '0', 'CCC')
INSERT INTO JobRotation VALUES('E4', 'Director', '2', '1', 'DDD')

Select * From Account
Select * From Employees
Select * From Departments
Select * From Sections
Select * From Designation
Select * From Vacancies
Select * From VacancyFillDetails
Select * From JobRotation

select * from Employees as E 
JOIN VacancyFillDetails as V ON E.EMPID = V.EMPID and V.STATUS = 0 
JOIN Departments as D on D.DEPTNO = E.DEPTNO
JOIN Designation as Ds on E.DESIGNID = Ds.DESIGNID
JOIN Sections as S on E.SECTID = S.SECTID
WHERE V.EMPID = ?